﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Data;
namespace XamarinAndroidSample.WebApi.DAL
{
    public class SqlHelper
    {
        string connectionString = "Data Source=127.0.0.1;Initial Catalog=XamarinAndroidSample;Persist Security Info=True;User ID=sa;Password=123456";
        SqlConnection conn;
        private static SqlHelper insance;
        /// <summary>
        /// 实例化对象
        /// </summary>
        /// <returns></returns>
        public static SqlHelper GetInstance()
        {
            if (insance == null)
            {
                insance = new SqlHelper();
            }
            return insance;
        }


        /// <summary>
        /// 执行sql语句返回datatable
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public DataTable doSelect(string sql)
        {
            SqlDataAdapter adapter = null;
            using (conn = new SqlConnection(connectionString))
            {
                try
                {
                    conn.Open();
                    adapter = new SqlDataAdapter(sql, conn);
                    DataSet ds = new DataSet();
                    adapter.Fill(ds);
                    return ds.Tables[0];
                }
                catch (Exception exception)
                {
                    conn.Close();
                    throw new Exception(exception.ToString());
                }
            }
        }

        public DataRow getRowObj(string sql)
        {
            SqlDataAdapter adapter = null;
            using (conn = new SqlConnection(connectionString))
            {
                try
                {
                    conn.Open();
                    adapter = new SqlDataAdapter(sql, conn);
                    DataSet ds = new DataSet();
                    adapter.Fill(ds);
                    return ds.Tables[0].Rows[0];
                }
                catch (Exception exception)
                {
                    conn.Close();
                    throw new Exception(exception.ToString());
                }
            }
        }

        /// <summary>
        /// 执行sql语句返回第一行第一列查询结果
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public string ReturnStr(string sql)
        {
            SqlDataAdapter adapter = null;
            using (conn = new SqlConnection(connectionString))
            {
                try
                {
                    conn.Open();
                    adapter = new SqlDataAdapter(sql, conn);
                    DataSet ds = new DataSet();
                    adapter.Fill(ds);
                    return ds.Tables[0].Rows[0][0].ToString();
                }
                catch (Exception exception)
                {
                    conn.Close();
                    throw new Exception(exception.ToString());
                }
            }
        }

        /// <summary>
        /// 执行查询，获取结果集的count总数
        /// </summary>
        /// <returns></returns>
        public int getCount(string sql)
        {
            SqlCommand command = null;
            using (conn = new SqlConnection(connectionString))
            {
                conn.Open();
                command = new SqlCommand(sql, conn);
                try
                {
                    command.CommandText = sql;
                    command.CommandType = CommandType.Text;
                    int count = (int)command.ExecuteScalar();
                    return count;
                }
                catch (Exception exception)
                {
                    conn.Close();
                    throw new Exception(exception.ToString());
                }
            }
        }
        /// <summary>
        /// 执行sql返回结果
        /// </summary>
        /// <param name="sql"></param>
        /// <returns>成功 true 失败 false</returns>
        public bool ExcuteSql(string sql)
        {
            SqlCommand command=null;
            using (conn = new SqlConnection(connectionString))
            {
                 command= new SqlCommand(sql, conn);
                try
                {
                    conn.Open();
                    int influence = command.ExecuteNonQuery();
                    if (influence > 0)
                    {
                        return true;
                    }
                    else
                    {
                        return false;
                    }
                }
                catch (Exception exception)
                {
                    conn.Close();
                    throw new Exception(exception.ToString());
                }
            }
        }
    }
}
